IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FdsHuman_UpdateStaffRoles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FdsHuman_UpdateStaffRoles]
GO
/****** Object:  StoredProcedure [dbo].[FdsHuman_UpdateStaffRoles]    Script Date: 11/25/2008 11:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Irina Kozlova
-- Create date: 11/25/2008
-- Description:	Updates Study Staff roles from Rex data; to be called from the Numan feed
-- =============================================
CREATE PROCEDURE dbo.FdsHuman_UpdateStaffRoles

AS
BEGIN

	SET NOCOUNT ON;

		UPDATE MAP Set ProtocolStaffTypeId = StgStaff.StaffTypeId,
			ModifiedDt = StgStaff.dtModified
		FROM Stg_ProtocolStaffing StgStaff JOIN ProtocolStaff_Map MAP
			on MAP.ProtocolNumber = StgStaff.FolderNumber
			AND StgStaff.AdmPersonId = MAP.AdmPersonId
		WHERE StgStaff.StaffTypeId != MAP.ProtocolStaffTypeId
			and MAP.ProtocolStaffTypeId != 13
			and MAP.IsCurrent = 1 
			and StgStaff.StaffTypeId != 1
			and MAP.ProtocolStaffTypeId !=1

SET NOCOUNT OFF;

END

GO